SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[uspGetCreateTableScript]
	@existingTableName	SYSNAME,				-- Input parameter which will be taking in the existing table name
	@newTableName		SYSNAME,				-- chaning the table name to this one in the final create table script
	--@DbName			    varchar(50),
	@SchemaName			sysname,
	@outSchema			VARCHAR(10),
	@outName	VARCHAR(50),
	@scriptOutput		VARCHAR(8000) OUTPUT	-- this variable will have the final CREATE TABLE ..script in it which can be used in other procedures
	
AS
SET NOCOUNT ON
/* DECLARATIONS */
DECLARE @sql VARCHAR(8000)						-- Variable will return either with the CREATE TABLE.. script or error message
DECLARE @table VARCHAR(100)            
declare @cols TABLE (datatype VARCHAR(50))  
 


IF EXISTS (SELECT * FROM FSFundModel.[INFORMATION_SCHEMA].[COLUMNS] WHERE Table_Name = @existingTableName AND TABLE_SCHEMA = @SchemaName)           
BEGIN            
        
	INSERT INTO @cols VALUES('bit')          
	INSERT INTO @cols VALUES('binary')          
	INSERT INTO @cols VALUES('bigint')          
	INSERT INTO @cols VALUES('int')          
	INSERT INTO @cols VALUES('float')          
	INSERT INTO @cols VALUES('datetime')          
	INSERT INTO @cols VALUES('text')          
	INSERT INTO @cols VALUES('image')          
	INSERT INTO @cols VALUES('uniqueidentifier')          
	INSERT INTO @cols VALUES('smalldatetime')          
	INSERT INTO @cols VALUES('tinyint')          
	INSERT INTO @cols VALUES('smallint')          
	INSERT INTO @cols VALUES('sql_variant')          
	INSERT INTO @cols VALUES('date')  
	           
	SET @sql=''            
	SELECT @sql=@sql+   CHAR(13) +          
	CASE WHEN charindex('(',@sql,1)<=0 THEN '(' ELSE '' END +Column_Name + ' ' +Data_Type +             
	CASE WHEN Data_Type IN (SELECT datatype FROM @cols) THEN '' ELSE  '(' END+
	CASE WHEN data_type IN ('real','money','decimal','numeric')  THEN cast(isnull(numeric_precision,'') AS VARCHAR)+','+
	CASE WHEN data_type IN ('real','money','decimal','numeric') THEN cast(isnull(Numeric_Scale,'') AS VARCHAR) END
	WHEN data_type IN ('char','nvarchar','VARCHAR','nchar') THEN cast(isnull(Character_Maximum_Length,'') AS VARCHAR)       ELSE '' END+
	CASE WHEN Data_Type IN (SELECT datatype FROM @cols)THEN '' ELSE  ')' END+
	CASE WHEN Is_Nullable='No' THEN ' Not null,' ELSE ' null,' END     
	FROM FSFundModel.[Information_Schema].[COLUMNS] WHERE Table_Name = @existingTableName   AND TABLE_SCHEMA = @SchemaName          
	 
	  
	SELECT  @table = 'CREATE TYPE ' + @outSchema+'.'+@outName+'.'+ @newTableName + ' AS TABLE ' FROM FSFundModel.[Information_Schema].[COLUMNS] WHERE table_Name = @existingTableName AND TABLE_SCHEMA =@SchemaName
	SELECT @sql = @table + SUBSTRING (@sql, 1, LEN(@sql)-1) +' )'  -- Adding the ending bracket in to the code and assigning it to the return variable
	--SELECT @sql AS DDL
	--RETURN @sql
	SET @scriptOutput = @sql
  
END           

ELSE
BEGIN        
	SET @sql = 'The table ' + @existingTableName + ' does not exist.'    
	SET @scriptOutput = @sql
END
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo', 'SCHEMA', N'dbo', 'PROCEDURE', N'uspGetCreateTableScript', NULL, NULL
GO
